package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.dto.journey.CreateJourneyDto
import com.opennews.openplatform.familyexpenseapi.dto.journey.UpdateJourneyDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.springframework.stereotype.Service

@Service
class JourneyService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val regionService: RegionService,
) {
    /**
     * Queries all journeys which are associated with the provided account group.
     *
     * @return The list of map instances which contain the following keys:
     * id: String of the journey id.
     * startDate: Start date of the journey.
     * endDate: End date of the journey.
     * regionId: String of the region id.
     * regionName: String of the name of the region.
     * regionTitle: String of the title of the region.
     * regionFlagUrl: String of the flag URL of the region.
     */
    fun queryListByAccountGroup(accountGroupId: String, pageSize: Int, pageIndex: Int): List<Map<String, Any>> {
        return dslContext
            .select(
                JOURNEY.ID.`as`("id"),
                JOURNEY.START_DATE.`as`("startDate"),
                JOURNEY.END_DATE.`as`("endDate"),
                REGION.ID.`as`("regionId"),
                REGION.NAME.`as`("regionName"),
                REGION.TITLE.`as`("regionTitle"),
            )
            .from(JOURNEY)
            .join(REGION).on(JOURNEY.REGION_ID.eq(REGION.ID))
            .where(JOURNEY.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .orderBy(JOURNEY.START_DATE.desc())
            .limit(pageSize)
            .offset(pageIndex * pageSize)
            .fetch()
            .map { row ->
                val item = row.intoMap()
                item["regionFlagUrl"] = regionService.getFlagUrl(item["regionName"].toString())

                item
            }
    }

    /**
     * Adds new journey for account group.
     *
     * @param accountGroupId: The string of user account group id.
     * @param data:           The object which contains data for the journey.
     */
    fun add(accountGroupId: String, @Valid data: CreateJourneyDto) {
        // Creates new Journey with posted data.
        dslContext.newRecord(JOURNEY).run {
            from(data)
            this.accountGroupId = accountGroupId
            regionId = data.region.id?.asString()
            insert()
        }
    }

    /**
     * Updates journey info based on provided map data and its id.
     * The map keys must match journey property names.
     * If the map item will be ignored if its value is null.
     *
     * @param accountGroupId: The string of user account group id.
     * @param data            The object containing data to update the journey.
     *                        The object must contain a key for id.
     */
    fun update(accountGroupId: String, @Valid data: UpdateJourneyDto) {
        dslContext.update(JOURNEY)
            .set(JOURNEY.START_DATE, data.startDate)
            .set(JOURNEY.END_DATE, data.endDate)
            .where(
                JOURNEY.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(JOURNEY.ID.eq(data.id))
            )
            .execute()
    }

    /**
     * Deletes the Journey with provided id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param id:             The journey id.
     */
    fun delete(accountGroupId: String, id: String): Boolean {
        var isDeleted = false

        // Queries the input id and makes sure it belongs to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can void other account group users calling the API directly and providing an id they don't actually own.
        // AKA parallel authentication.
        // Checks if this journey contains any income/expense.
        if (!dslContextWrapper.exists(
                JOURNEY_INCOME_EXPENSE_DETAIL, JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.eq(id))
            )
        ) {
            // If this journey contains no income/expense then deletes it.
            dslContext.deleteFrom(JOURNEY)
                .where(
                    JOURNEY.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(JOURNEY.ID.eq(id))
                )
                .execute()

            isDeleted = true
        }

        return isDeleted
    }
}